import sqlite3
import hashlib
import os
import json
from datetime import datetime
from typing import Optional, List, Dict, Any
import uuid

class DatabaseManager:
    """数据库管理器，负责SQLite数据库的初始化和连接管理"""
    
    def __init__(self, db_path: str = 'data/app.db'):
        self.db_path = db_path
        self._ensure_data_dir()
        self._init_database()
    
    def _ensure_data_dir(self):
        """确保数据目录存在"""
        os.makedirs(os.path.dirname(self.db_path), exist_ok=True)
    
    def _init_database(self):
        """初始化数据库表结构"""
        with sqlite3.connect(self.db_path) as conn:
            cursor = conn.cursor()
            
            # 创建用户表
            cursor.execute('''
                CREATE TABLE IF NOT EXISTS users (
                    student_id TEXT PRIMARY KEY,
                    name TEXT NOT NULL,
                    password_hash TEXT NOT NULL,
                    salt TEXT NOT NULL,
                    college TEXT,
                    major TEXT,
                    phone TEXT,
                    avatar_url TEXT,
                    profile TEXT,
                    resume_data TEXT,
                    grade INTEGER,
                    created_at TEXT,
                    last_login TEXT,
                    unread_news TEXT
                )
            ''')
            
            # 创建聊天历史表
            cursor.execute('''
                CREATE TABLE IF NOT EXISTS chat_conversations (
                    id TEXT PRIMARY KEY,
                    student_id TEXT NOT NULL,
                    title TEXT NOT NULL,
                    created_at TEXT NOT NULL,
                    updated_at TEXT NOT NULL,
                    FOREIGN KEY (student_id) REFERENCES users (student_id) ON DELETE CASCADE
                )
            ''')
            
            # 创建聊天消息表
            cursor.execute('''
                CREATE TABLE IF NOT EXISTS chat_messages (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    conversation_id TEXT NOT NULL,
                    type TEXT NOT NULL,
                    content TEXT NOT NULL,
                    timestamp TEXT NOT NULL,
                    FOREIGN KEY (conversation_id) REFERENCES chat_conversations (id) ON DELETE CASCADE
                )
            ''')
            
            # 创建竞赛表
            cursor.execute('''
                CREATE TABLE IF NOT EXISTS competitions (
                    id INTEGER PRIMARY KEY,
                    name TEXT NOT NULL,
                    description TEXT,
                    status TEXT NOT NULL,
                    categories TEXT,
                    start_date TEXT,
                    end_date TEXT,
                    rating TEXT,
                    organizer TEXT,
                    max_team_size INTEGER DEFAULT 3,
                    url TEXT,
                    image_url TEXT,
                    detail TEXT,
                    created_at TEXT DEFAULT CURRENT_TIMESTAMP,
                    updated_at TEXT DEFAULT CURRENT_TIMESTAMP
                )
            ''')
            
            # 创建竞赛分类表（用于优化搜索）
            cursor.execute('''
                CREATE TABLE IF NOT EXISTS competition_categories (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    competition_id INTEGER NOT NULL,
                    category TEXT NOT NULL,
                    FOREIGN KEY (competition_id) REFERENCES competitions (id) ON DELETE CASCADE
                )
            ''')
            
            # 创建索引以提高查询性能
            cursor.execute('CREATE INDEX IF NOT EXISTS idx_conversations_student_id ON chat_conversations (student_id)')
            cursor.execute('CREATE INDEX IF NOT EXISTS idx_messages_conversation_id ON chat_messages (conversation_id)')
            cursor.execute('CREATE INDEX IF NOT EXISTS idx_competitions_status ON competitions (status)')
            cursor.execute('CREATE INDEX IF NOT EXISTS idx_competitions_rating ON competitions (rating)')
            cursor.execute('CREATE INDEX IF NOT EXISTS idx_competitions_start_date ON competitions (start_date)')
            cursor.execute('CREATE INDEX IF NOT EXISTS idx_competitions_end_date ON competitions (end_date)')
            cursor.execute('CREATE INDEX IF NOT EXISTS idx_competition_categories_competition_id ON competition_categories (competition_id)')
            cursor.execute('CREATE INDEX IF NOT EXISTS idx_competition_categories_category ON competition_categories (category)')
            
            conn.commit()
    
    def get_connection(self):
        """获取数据库连接"""
        conn = sqlite3.connect(self.db_path, timeout=5.0, isolation_level=None, check_same_thread=False)
        try:
            # 基础性能与一致性配置
            conn.execute('PRAGMA journal_mode=WAL;')
            conn.execute('PRAGMA synchronous=NORMAL;')
            conn.execute('PRAGMA foreign_keys=ON;')
            conn.execute('PRAGMA busy_timeout=5000;')
        except Exception:
            pass
        return conn

class PasswordManager:
    """密码管理器，负责密码加密和验证"""
    
    @staticmethod
    def hash_password(password: str) -> tuple[str, str]:
        """对密码进行加密，返回哈希值和盐值"""
        salt = os.urandom(32).hex()
        password_hash = hashlib.pbkdf2_hmac(
            'sha256', 
            password.encode('utf-8'), 
            salt.encode('utf-8'), 
            100000  # 迭代次数
        ).hex()
        return password_hash, salt
    
    @staticmethod
    def verify_password(password: str, password_hash: str, salt: str) -> bool:
        """验证密码是否正确"""
        try:
            test_hash = hashlib.pbkdf2_hmac(
                'sha256', 
                password.encode('utf-8'), 
                salt.encode('utf-8'), 
                100000
            ).hex()
            return test_hash == password_hash
        except Exception:
            return False

class UserDB:
    """用户数据库操作类"""
    
    def __init__(self, db_manager: DatabaseManager):
        self.db_manager = db_manager
    
    def create_user(self, student_id: str, name: str, password: str, 
                   college: str = "计算机科学与技术学院", major: str = "人工智能", 
                   phone: str = None) -> tuple[bool, str]:
        """创建新用户"""
        try:
            # 检查用户是否已存在
            if self.get_user_by_student_id(student_id):
                return False, "用户已存在"
            
            # 加密密码
            password_hash, salt = PasswordManager.hash_password(password)
            
            # 初始化未读通知ID列表
            unread_ids = []
            try:
                notifications_path = 'data/notifications.json'
                if os.path.exists(notifications_path):
                    with open(notifications_path, 'r', encoding='utf-8') as nf:
                        notifications_data = json.load(nf)
                        unread_ids = [str(item.get('id')) for item in notifications_data if 'id' in item]
            except Exception:
                unread_ids = []
            
            with self.db_manager.get_connection() as conn:
                cursor = conn.cursor()
                cursor.execute('''
                    INSERT INTO users (
                        student_id, name, password_hash, salt, college, major, phone,
                        avatar_url, grade, created_at, last_login, unread_news
                    ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
                ''', (
                    student_id, name, password_hash, salt, college, major, phone,
                    "images/default_avatar.jpeg", int(student_id[:4]), 
                    datetime.now().isoformat(), datetime.now().isoformat(),
                    json.dumps(unread_ids)
                ))
                conn.commit()
            
            return True, "注册成功"
        except Exception as e:
            print(f"创建用户错误: {e}")
            return False, "注册失败，请重试"
    
    def authenticate_user(self, student_id: str, password: str) -> Optional[Dict[str, Any]]:
        """用户认证"""
        try:
            with self.db_manager.get_connection() as conn:
                cursor = conn.cursor()
                cursor.execute('''
                    SELECT student_id, name, password_hash, salt, college, major, phone,
                           avatar_url, profile, resume_data, grade, created_at, last_login, unread_news
                    FROM users WHERE student_id = ?
                ''', (student_id,))
                
                row = cursor.fetchone()
                if not row:
                    return None
                
                # 验证密码
                if not PasswordManager.verify_password(password, row[2], row[3]):
                    return None
                
                # 更新最后登录时间
                cursor.execute('UPDATE users SET last_login = ? WHERE student_id = ?', 
                             (datetime.now().isoformat(), student_id))
                conn.commit()
                
                # 返回用户信息（不包含密码）
                return {
                    'student_id': row[0],
                    'name': row[1],
                    'college': row[4],
                    'major': row[5],
                    'phone': row[6],
                    'avatar_url': row[7],
                    'profile': row[8],
                    'resume': json.loads(row[9]) if row[9] else None,
                    'grade': row[10],
                    'created_at': row[11],
                    'last_login': row[12],
                    'unread_news': json.loads(row[13]) if row[13] else []
                }
        except Exception as e:
            print(f"用户认证错误: {e}")
            return None
    
    def get_user_by_student_id(self, student_id: str) -> Optional[Dict[str, Any]]:
        """根据学号获取用户信息"""
        try:
            with self.db_manager.get_connection() as conn:
                cursor = conn.cursor()
                cursor.execute('''
                    SELECT student_id, name, college, major, phone, avatar_url, profile,
                           resume_data, grade, created_at, last_login, unread_news
                    FROM users WHERE student_id = ?
                ''', (student_id,))
                
                row = cursor.fetchone()
                if not row:
                    return None
                
                return {
                    'student_id': row[0],
                    'name': row[1],
                    'college': row[2],
                    'major': row[3],
                    'phone': row[4],
                    'avatar_url': row[5],
                    'profile': row[6],
                    'resume': json.loads(row[7]) if row[7] else None,
                    'grade': row[8],
                    'created_at': row[9],
                    'last_login': row[10],
                    'unread_news': json.loads(row[11]) if row[11] else []
                }
        except Exception as e:
            print(f"获取用户错误: {e}")
            return None
    
    def update_user(self, student_id: str, **kwargs) -> bool:
        """更新用户信息"""
        try:
            # 构建更新字段
            update_fields = []
            params = []
            
            allowed_fields = ['name', 'college', 'major', 'phone', 'profile', 'avatar_url']
            for field, value in kwargs.items():
                if field in allowed_fields:
                    update_fields.append(f"{field} = ?")
                    params.append(value)
            
            if not update_fields:
                return False
            
            params.append(student_id)
            
            with self.db_manager.get_connection() as conn:
                cursor = conn.cursor()
                cursor.execute(f'''
                    UPDATE users SET {', '.join(update_fields)} WHERE student_id = ?
                ''', params)
                conn.commit()
                
                return cursor.rowcount > 0
        except Exception as e:
            print(f"更新用户错误: {e}")
            return False
    
    def update_resume(self, student_id: str, resume_data: Dict[str, Any] = None) -> bool:
        """更新用户简历"""
        try:
            with self.db_manager.get_connection() as conn:
                cursor = conn.cursor()
                cursor.execute('''
                    UPDATE users SET resume_data = ? WHERE student_id = ?
                ''', (json.dumps(resume_data) if resume_data else None, student_id))
                conn.commit()
                return cursor.rowcount > 0
        except Exception as e:
            print(f"更新简历错误: {e}")
            return False
    
    def update_unread_news(self, student_id: str, unread_news: List[str]) -> bool:
        """更新用户未读通知列表"""
        try:
            with self.db_manager.get_connection() as conn:
                cursor = conn.cursor()
                cursor.execute('''
                    UPDATE users SET unread_news = ? WHERE student_id = ?
                ''', (json.dumps(unread_news), student_id))
                conn.commit()
                return cursor.rowcount > 0
        except Exception as e:
            print(f"更新未读通知错误: {e}")
            return False

class ChatHistoryDB:
    """聊天历史数据库操作类"""
    
    def __init__(self, db_manager: DatabaseManager):
        self.db_manager = db_manager
    
    def get_chat_history(self, student_id: str) -> List[Dict[str, Any]]:
        """获取用户的聊天历史"""
        try:
            with self.db_manager.get_connection() as conn:
                cursor = conn.cursor()
                cursor.execute('''
                    SELECT id, title, created_at, updated_at
                    FROM chat_conversations 
                    WHERE student_id = ?
                    ORDER BY updated_at DESC
                ''', (student_id,))
                
                conversations = []
                for row in cursor.fetchall():
                    conversation = {
                        'id': row[0],
                        'title': row[1],
                        'created_at': row[2],
                        'updated_at': row[3],
                        'messages': []
                    }
                    
                    # 获取对话中的消息
                    cursor.execute('''
                        SELECT type, content, timestamp
                        FROM chat_messages 
                        WHERE conversation_id = ?
                        ORDER BY timestamp ASC
                    ''', (row[0],))
                    
                    for msg_row in cursor.fetchall():
                        conversation['messages'].append({
                            'type': msg_row[0],
                            'content': msg_row[1],
                            'timestamp': msg_row[2]
                        })
                    
                    conversations.append(conversation)
                
                return conversations
        except Exception as e:
            print(f"获取聊天历史错误: {e}")
            return []
    
    def create_conversation(self, student_id: str, title: str) -> str:
        """创建新对话"""
        try:
            conversation_id = str(uuid.uuid4())
            current_time = datetime.now().isoformat()
            
            with self.db_manager.get_connection() as conn:
                cursor = conn.cursor()
                cursor.execute('''
                    INSERT INTO chat_conversations (id, student_id, title, created_at, updated_at)
                    VALUES (?, ?, ?, ?, ?)
                ''', (conversation_id, student_id, title, current_time, current_time))
                conn.commit()
            
            return conversation_id
        except Exception as e:
            print(f"创建对话错误: {e}")
            return None
    
    def add_message(self, conversation_id: str, message_type: str, content: str) -> bool:
        """添加消息到对话"""
        try:
            current_time = datetime.now().isoformat()
            
            with self.db_manager.get_connection() as conn:
                cursor = conn.cursor()
                
                # 添加消息
                cursor.execute('''
                    INSERT INTO chat_messages (conversation_id, type, content, timestamp)
                    VALUES (?, ?, ?, ?)
                ''', (conversation_id, message_type, content, current_time))
                
                # 更新对话的更新时间
                cursor.execute('''
                    UPDATE chat_conversations SET updated_at = ? WHERE id = ?
                ''', (current_time, conversation_id))
                
                conn.commit()
                return True
        except Exception as e:
            print(f"添加消息错误: {e}")
            return False
    
    def delete_conversation(self, student_id: str, conversation_id: str) -> bool:
        """删除对话"""
        try:
            with self.db_manager.get_connection() as conn:
                cursor = conn.cursor()
                cursor.execute('''
                    DELETE FROM chat_conversations 
                    WHERE id = ? AND student_id = ?
                ''', (conversation_id, student_id))
                conn.commit()
                return cursor.rowcount > 0
        except Exception as e:
            print(f"删除对话错误: {e}")
            return False
    
    def get_conversation_messages(self, conversation_id: str) -> List[Dict[str, Any]]:
        """获取对话的所有消息"""
        try:
            with self.db_manager.get_connection() as conn:
                cursor = conn.cursor()
                cursor.execute('''
                    SELECT type, content, timestamp
                    FROM chat_messages 
                    WHERE conversation_id = ?
                    ORDER BY timestamp ASC
                ''', (conversation_id,))
                
                messages = []
                for row in cursor.fetchall():
                    messages.append({
                        'type': row[0],
                        'content': row[1],
                        'timestamp': row[2]
                    })
                
                return messages
        except Exception as e:
            print(f"获取对话消息错误: {e}")
            return []

class CompetitionDB:
    """竞赛数据库操作类"""
    
    def __init__(self, db_manager: DatabaseManager):
        self.db_manager = db_manager
    
    def get_all_competitions(self) -> List[Dict[str, Any]]:
        """获取所有竞赛"""
        try:
            with self.db_manager.get_connection() as conn:
                cursor = conn.cursor()
                cursor.execute('''
                    SELECT id, name, description, status, categories, start_date, end_date,
                           rating, organizer, max_team_size, url, image_url, detail
                    FROM competitions
                    ORDER BY id ASC
                ''')
                
                competitions = []
                for row in cursor.fetchall():
                    competition = {
                        'id': row[0],
                        'name': row[1],
                        'description': row[2],
                        'status': row[3],
                        'categories': json.loads(row[4]) if row[4] else [],
                        'start_date': row[5],
                        'end_date': row[6],
                        'rating': row[7],
                        'organizer': row[8],
                        'max_team_size': row[9],
                        'url': row[10],
                        'image_url': row[11],
                        'detail': row[12]
                    }
                    competitions.append(competition)
                
                return competitions
        except Exception as e:
            print(f"获取竞赛列表错误: {e}")
            return []
    
    def get_competition_by_id(self, competition_id: int) -> Optional[Dict[str, Any]]:
        """根据ID获取竞赛详情"""
        try:
            with self.db_manager.get_connection() as conn:
                cursor = conn.cursor()
                cursor.execute('''
                    SELECT id, name, description, status, categories, start_date, end_date,
                           rating, organizer, max_team_size, url, image_url, detail
                    FROM competitions
                    WHERE id = ?
                ''', (competition_id,))
                
                row = cursor.fetchone()
                if not row:
                    return None
                
                return {
                    'id': row[0],
                    'name': row[1],
                    'description': row[2],
                    'status': row[3],
                    'categories': json.loads(row[4]) if row[4] else [],
                    'start_date': row[5],
                    'end_date': row[6],
                    'rating': row[7],
                    'organizer': row[8],
                    'max_team_size': row[9],
                    'url': row[10],
                    'image_url': row[11],
                    'detail': row[12]
                }
        except Exception as e:
            print(f"获取竞赛详情错误: {e}")
            return None
    
    def search_competitions(self, query: str = '', category: str = '', status: str = '', 
                          rating: str = '') -> List[Dict[str, Any]]:
        """搜索竞赛（优化版本）"""
        try:
            with self.db_manager.get_connection() as conn:
                cursor = conn.cursor()
                
                # 构建查询条件
                conditions = []
                params = []
                
                if query:
                    # 使用全文搜索优化
                    conditions.append('''
                        (name LIKE ? OR description LIKE ? OR organizer LIKE ? OR detail LIKE ?)
                    ''')
                    query_param = f'%{query}%'
                    params.extend([query_param, query_param, query_param, query_param])
                
                if category:
                    # 使用分类表进行精确搜索
                    conditions.append('''
                        id IN (
                            SELECT competition_id 
                            FROM competition_categories 
                            WHERE category = ?
                        )
                    ''')
                    params.append(category)
                
                if status:
                    conditions.append('status = ?')
                    params.append(status)
                
                if rating:
                    conditions.append('rating = ?')
                    params.append(rating)
                
                # 构建SQL查询
                sql = '''
                    SELECT id, name, description, status, categories, start_date, end_date,
                           rating, organizer, max_team_size, url, image_url, detail
                    FROM competitions
                '''
                
                if conditions:
                    sql += ' WHERE ' + ' AND '.join(conditions)
                
                sql += ' ORDER BY id ASC'
                
                cursor.execute(sql, params)
                
                competitions = []
                for row in cursor.fetchall():
                    competition = {
                        'id': row[0],
                        'name': row[1],
                        'description': row[2],
                        'status': row[3],
                        'categories': json.loads(row[4]) if row[4] else [],
                        'start_date': row[5],
                        'end_date': row[6],
                        'rating': row[7],
                        'organizer': row[8],
                        'max_team_size': row[9],
                        'url': row[10],
                        'image_url': row[11],
                        'detail': row[12]
                    }
                    competitions.append(competition)
                
                return competitions
        except Exception as e:
            print(f"搜索竞赛错误: {e}")
            return []
    
    def get_competitions_by_status(self, status: str) -> List[Dict[str, Any]]:
        """根据状态获取竞赛"""
        return self.search_competitions(status=status)
    
    def get_competitions_by_category(self, category: str) -> List[Dict[str, Any]]:
        """根据分类获取竞赛"""
        return self.search_competitions(category=category)
    
    def get_competitions_by_rating(self, rating: str) -> List[Dict[str, Any]]:
        """根据等级获取竞赛"""
        return self.search_competitions(rating=rating)
    
    def get_upcoming_competitions(self) -> List[Dict[str, Any]]:
        """获取即将开始的竞赛"""
        try:
            with self.db_manager.get_connection() as conn:
                cursor = conn.cursor()
                cursor.execute('''
                    SELECT id, name, description, status, categories, start_date, end_date,
                           rating, organizer, max_team_size, url, image_url, detail
                    FROM competitions
                    WHERE status = 'upcoming'
                    ORDER BY start_date ASC
                    LIMIT 10
                ''')
                
                competitions = []
                for row in cursor.fetchall():
                    competition = {
                        'id': row[0],
                        'name': row[1],
                        'description': row[2],
                        'status': row[3],
                        'categories': json.loads(row[4]) if row[4] else [],
                        'start_date': row[5],
                        'end_date': row[6],
                        'rating': row[7],
                        'organizer': row[8],
                        'max_team_size': row[9],
                        'url': row[10],
                        'image_url': row[11],
                        'detail': row[12]
                    }
                    competitions.append(competition)
                
                return competitions
        except Exception as e:
            print(f"获取即将开始的竞赛错误: {e}")
            return []
    
    def get_all_categories(self) -> List[str]:
        """获取所有竞赛分类"""
        try:
            with self.db_manager.get_connection() as conn:
                cursor = conn.cursor()
                cursor.execute('''
                    SELECT DISTINCT category 
                    FROM competition_categories 
                    ORDER BY category
                ''')
                
                return [row[0] for row in cursor.fetchall()]
        except Exception as e:
            print(f"获取竞赛分类错误: {e}")
            return []
    
    def update_competition_status(self) -> bool:
        """更新竞赛状态"""
        try:
            with self.db_manager.get_connection() as conn:
                cursor = conn.cursor()
                
                # 更新状态为已结束的竞赛
                cursor.execute('''
                    UPDATE competitions 
                    SET status = 'finished', updated_at = CURRENT_TIMESTAMP
                    WHERE end_date < date('now') AND status != 'finished'
                ''')
                
                # 更新状态为进行中的竞赛
                cursor.execute('''
                    UPDATE competitions 
                    SET status = 'ongoing', updated_at = CURRENT_TIMESTAMP
                    WHERE start_date <= date('now') AND end_date >= date('now') AND status != 'ongoing'
                ''')
                
                # 更新状态为即将开始的竞赛
                cursor.execute('''
                    UPDATE competitions 
                    SET status = 'upcoming', updated_at = CURRENT_TIMESTAMP
                    WHERE start_date > date('now') AND status != 'upcoming'
                ''')
                
                conn.commit()
                return True
        except Exception as e:
            print(f"更新竞赛状态错误: {e}")
            return False

# 全局数据库管理器实例
db_manager = DatabaseManager()
user_db = UserDB(db_manager)
chat_history_db = ChatHistoryDB(db_manager)
competition_db = CompetitionDB(db_manager)
